package com.edu.dao.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.edu.dao.BaseDao;
import com.edu.dao.UserDao;
import com.edu.entity.User;

public class UserDaoImpl extends BaseDao implements UserDao {
	Connection con = null;//数据库连接对象
	User user = null;//用户对象
	PreparedStatement pstm = null;//预编译语句对象
	ResultSet rs = null;//结果集对象
	int row = 0;//操作结果

	//实现用户登录功能
	@Override
	public User Login(String username, String userpass) {
		// TODO Auto-generated method stub
		con = BaseDao.getConn();//获取数据库连接
		String sql = "select * from user where username = ? and userpass = ?";//sql查询语句
		try {
			pstm = con.prepareStatement(sql);//建立预编译语句
			pstm.setObject(1,username);//设置第一个占位符的值为用户名
			pstm.setObject(2,userpass);//设置第二个占位符的值为密码
			
			rs = pstm.executeQuery();//执行查询操作
			if (rs.next()) {//如果存在用户，则将查询结果存放到User对象中
				user = new User();
				user.setId(rs.getInt("id"));//设置User对象的id属性
				user.setUsername(rs.getString("username"));//设置User对象的用户名属性
				user.setUserpass(rs.getString("userpass"));//设置User对象的密码属性
				user.setSex(rs.getString("sex"));//设置User对象的性别属性
				user.setPhone(rs.getString("phone"));//设置User对象的电话号码属性
				
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			try {
				BaseDao.closeAll(con, pstm, rs);//关闭ResultSet，PreparedStatement和Connection对象
			}catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		return user;//返回User对象
	}

	//实现用户注册功能
	@Override
	public int register(User user) {
		// TODO Auto-generated method stub
		con = BaseDao.getConn();//获取数据库连接
		String sql = "insert into User(username,userpass,sex,phone)value(?,?,?,?)";//sql插入语句
		try {
			pstm = con.prepareStatement(sql);//建立预编译语句
			pstm.setObject(1,user.getUsername());//设置第一个占位符的值为用户名
			pstm.setObject(2,user.getUserpass());//设置第二个占位符的值为密码
			pstm.setObject(3,user.getSex());//设置第三个占位符的值为性别
			pstm.setObject(5,user.getPhone());//设置第五个占位符的值为电话
			row = pstm.executeUpdate();//执行插入操作
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			try {
				BaseDao.closeAll(con, pstm);
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		
		return row;//返回操作结果
	}

	@Override
	public List<User> selectUserAll() {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM user";
		ResultSet rs=super.queryResultSet(sql);
		List<User> list = new ArrayList<User>();
		try {
			while(rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setUserpass(rs.getString("userpass"));
				user.setSex(rs.getString("sex"));
				user.setPhone(rs.getString("phone"));
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(super.conn, super.ps,rs);
		}
		return list;
	}

	@Override
	public int deleteById(int id) {
		// TODO Auto-generated method stub
		String sql="delete FROM USER WHERE id="+id;
		System.out.println(sql);
		int rowNum = super.executeUpdate(sql);
		return rowNum;
	}

	@Override
	public int updateById(User user) {
		// TODO Auto-generated method stub
		String sql="UPDATE USER SET username=?,userpass=?,sex=?,phone=? WHERE id=?";
		System.out.println(sql);
		
		Object[] param = {user.getUsername(),user.getUserpass(),user.getSex(),user.getPhone(),user.getId()};
		int rowNum = super.executeUpdate(sql,param);
		System.out.println("修改");
		return rowNum;
	}

	@Override
	public int deleteByIds(String ids) {
		// TODO Auto-generated method stub
		String sql="delete FROM USER WHERE id in ("+ids+")";
		System.out.println(sql);
		int rowNum = super.executeUpdate(sql);
		return rowNum;
	}

	@Override
	public int add(User user) {
		// TODO Auto-generated method stub
		String sql="INSERT INTO USER (username,userpass, sex, phone)VALUES (?,?,?,?)";
		System.out.println(sql);
		Object[] param = {user.getUsername(),user.getUserpass(),user.getSex(),user.getPhone()};
		int rowNum = super.executeUpdate(sql,param);
		return rowNum;
	}

	@Override
	public List<User> selectByUser(User u) {
		// TODO Auto-generated method stub
		/*
		String id = "";
		String age = "";
		if(u.getId() != -1) {
			id=u.getId()+"";
		}
		if(u.getAge() != -1) {
			age=String.valueOf(u.getAge());
		}
		String sql = "SELECT * FROM user "
				+ "where id like '%"+id+"%' "
				+ "and username like '%"+u.getUserName()+"%' "
				+ "and userpass like '%"+u.getUserPass()+"%' "
				+ "and sex like '%"+u.getSex()+"%' "
				+ "and age like '%"+age+"%'";
		*/
		String sql = "SELECT * FROM user WHERE 1=1 ";
		if(u.getId() != -1) {
			sql += "AND id like '%"+u.getId()+"%' ";
		}
		if(!"".equals(u.getUsername())) {
			sql += "AND username like '%"+u.getUsername()+"%' ";
		}
		if(!"".equals(u.getUserpass())) {
			sql += "AND userpass like '%"+u.getUserpass()+"%' ";
		}
		if(!"".equals(u.getSex())) {
			sql += "AND sex like '%"+u.getSex()+"%' ";
		}
		if(!"".equals(u.getPhone())) {
			sql += "AND phone like '%"+u.getPhone()+"%' ";
		}
		
		System.out.println(sql);
		
		
		ResultSet rs=super.queryResultSet(sql);
		List<User> list = new ArrayList<User>();
		try {
			while(rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setUserpass(rs.getString("userpass"));
				user.setSex(rs.getString("sex"));
				user.setPhone(rs.getString("phone"));
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(super.conn, super.ps,rs);
		}
		return list;
	}

}
